#!/usr/bin/env python3
#
# Data cobbler for weathercomp.
#
# This is an early implementation that uses a temporary view rather than
# manually creating a table.  Manually creating the table takes a good while,
# but querying the view isn't any better.  Since the temporary table is only
# created once and queries on that are fast enough, this solution is not used
# for the time being.

import collections
import datetime
import sqlite3

WeatherRecord = collections.namedtuple('WeatherRecord', 
                                       ['location', 
                                        'date',
                                        'source', 
                                        'tempmin', 'tempmax', 
                                        'tempmin1', 'tempmin2', 'tempmin4',
                                        'tempmax1', 'tempmax2', 'tempmax4'])

class Weather:
    def __init__(self, db='weathercomp.db'):
        """Create a Weather instance.

        Cobbles together data from three tables into one big table with rows
        matching a WeatherRecord namedtuple.

        """
#        self.view = sqlite3.connect(':memory:')
#        self.view.row_factory = sqlite3.Row
        con = sqlite3.connect(db)
#        con.row_factory = sqlite3.Row

        # self.view.execute("""
        #     CREATE TABLE Weather (
        #         Location TEXT, 
        #         Date TEXT,
        #         Source TEXT, 
        #         TempMin INTEGER, TempMax INTEGER,
        #         TempMin1 INTEGER, TempMin2 INTEGER, TempMin4 INTEGER,
        #         TempMax1 INTEGER, TempMax2 INTEGER, TempMax4 INTEGER
        #     );""")

        viewquery = """
CREATE TEMP VIEW Weather AS
SELECT l.LocID as LocID, l.Name as loc, oneday.Source as Source, Date, 
TempMin, TempMax, 
TempMin1, TempMin2, TempMin4,
TempMax1, TempMax2, TempMax4
FROM Report as r, Location as l, 
    (SELECT ForecastDate, LocID, Source, TempMin as TempMin1, TempMax as TempMax1
    FROM Forecast
    WHERE julianday(ForecastDate) - julianday(ObservationDate) = 1) 
    as oneday,

    (SELECT ForecastDate, LocID, Source, TempMin as TempMin2, TempMax as TempMax2
    FROM Forecast
    WHERE julianday(ForecastDate) - julianday(ObservationDate) = 2) 
    as twoday,

    (SELECT ForecastDate, LocID, Source, TempMin as TempMin4, TempMax as TempMax4
    FROM Forecast
    WHERE julianday(ForecastDate) - julianday(ObservationDate) = 4) 
    as fourday

WHERE l.LocID = r.LocID
AND oneday.ForecastDate = r.Date
AND oneday.LocID = r.LocID
AND twoday.ForecastDate = r.Date
AND twoday.LocID = r.LocID
AND fourday.ForecastDate = r.Date
AND fourday.LocID = r.LocID

AND oneday.Source = twoday.Source
AND twoday.Source = fourday.Source

ORDER BY l.LocID, Date"""

        con.execute(viewquery)
        for row in con.execute("SELECT * FROM Weather WHERE loc = 'Auckland, New Zealand' AND Source = 'weatherbug' LIMIT 50"):
            print(row)
        for row in con.execute("SELECT * FROM Weather WHERE loc = 'Auckland, New Zealand' AND Source = 'weatherbug' LIMIT 50"):
            print(row)

        # reportquery = """
        #     SELECT Location.LocID as LocID, Location.Name as loc, Date, 
        #     TempMin, TempMax 
        #         FROM Report, Location
        #         WHERE Location.LocID = Report.LocID
        #         ORDER BY Location.LocID, Date;"""
        # forecastquery = """
        #     SELECT Source, julianday(:date) - julianday(ObservationDate) as daysahead, 
        #     TempMin, TempMax 
        #         FROM Forecast
        #         WHERE ForecastDate = :date AND LocID = :locid;"""
        # insertquery = """
        #     INSERT INTO Weather VALUES (
        #         :location, :date, :source,
        #         :tempmin, :tempmax,
        #         :tempmin1, :tempmin2, :tempmin4,
        #         :tempmax1, :tempmax2, :tempmax4
        #     );"""

        # records = []
        # for report in con.execute(reportquery):
        #     forecasts = dict()
        #     for forecast in con.execute(forecastquery, {
        #             'date': report['Date'], 
        #             'locid': report['LocID']
        #             }):
        #         source = forecast['Source']
        #         if source not in forecasts:
        #             forecasts[source] = {'min': {1: 0, 2: 0, 4: 0}, 'max': {1: 0, 2: 0, 4: 0}}
        #         forecasts[source]['min'][forecast['daysahead']] = forecast['TempMin']
        #         forecasts[source]['max'][forecast['daysahead']] = forecast['TempMax']
        #     for source, minmax in forecasts.items():
        #          records.append({
        #                 'location': report['loc'],
        #                 'date': report['Date'],
        #                 'source': source,
        #                 'tempmin': report['TempMin'],
        #                 'tempmax': report['TempMax'],
        #                 'tempmin1': minmax['min'][1],
        #                 'tempmin2': minmax['min'][2],
        #                 'tempmin4': minmax['min'][4],
        #                 'tempmax1': minmax['max'][1],
        #                 'tempmax2': minmax['max'][2],
        #                 'tempmax4': minmax['max'][4]
        #                 })
        # self.view.executemany(insertquery, records)
        # self.view.commit()
        # con.close()

    def locations(self):
        locquery = """SELECT DISTINCT Location FROM Weather"""
        return [row['Location'] for row in self.con.execute(locquery)]

    def sources(self):
        sourcequery = """SELECT DISTINCT Source FROM WEATHER"""
        return [row['Source'] for row in self.con.execute(sourcequery)]

    def records(self, location="%", source="%", 
                mindate=datetime.date(1000, 1, 1), 
                maxdate=datetime.date(datetime.MAXYEAR, 1, 1)):
        record_query = """
            SELECT Location, Date, Source, 
            TempMin, TempMax, 
            TempMin1, TempMin2, TempMin4, 
            TempMax1, TempMax2, TempMax4 
            FROM Weather
                WHERE Location LIKE :location
                AND Source LIKE :source
                AND julianday(Date) >= julianday(:mindate)
                AND julianday(Date) <= julianday(:maxdate);"""
        if hasattr(mindate, 'strftime'):
            mindate = mindate.strftime('%Y-%m-%d')
        if hasattr(maxdate, 'strftime'):
            maxdate = maxdate.strftime('%Y-%m-%d')

        args = {
            'location': location,
            'source': source,
            'mindate': mindate,
            'maxdate': maxdate
            }

        return [WeatherRecord(*row) for row in self.view.execute(record_query, args)]

w = Weather()
# print('Sources:')
# print(w.sources())
# print('Locations:')
# print(w.locations())

# # Fetch records from Auckland, with forecasts from weatherbug over the past seven days.
# print(w.records(
#         location='Auckland, New Zealand', 
#         source='weatherbug',
#         mindate=datetime.date.today() - datetime.timedelta(days=7)))


